﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for ManageTimeTable
/// </summary>
public class ManageTimeTable
{

    public int deleteTimeTable(TimeTable timetable)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("deleteTimeTable", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDTimeTable", SqlDbType.Int).Value = timetable.IDTimeTable;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }

    public int updateNewTimeTable(TimeTable timetable)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("editTimeTable", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@IDTimeTable", SqlDbType.Int).Value = timetable.IDTimeTable;
        cmd.Parameters.Add("@IDBusTrip", SqlDbType.Int).Value = timetable.IDBusStrip;
        cmd.Parameters.Add("@IDDriver", SqlDbType.Int).Value = timetable.IDDriver;
        cmd.Parameters.Add("@IDBus", SqlDbType.Int).Value = timetable.IDBus;
        cmd.Parameters.Add("@TimeStart", SqlDbType.DateTime).Value = timetable.TimeStart;
        cmd.Parameters.Add("@TimeStopExpected", SqlDbType.DateTime).Value = timetable.TimeStopExpected;
        cmd.Parameters.Add("@Direction", SqlDbType.NVarChar).Value = timetable.Direction;
        cmd.Parameters.Add("@Money", SqlDbType.Float).Value = timetable.Money;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }

    public int insertNewTimeTable(TimeTable timetable)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("insertNewTimeTable", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDBusTrip", SqlDbType.Int).Value = timetable.IDBusStrip;
        cmd.Parameters.Add("@IDDriver", SqlDbType.Int).Value = timetable.IDDriver;
        cmd.Parameters.Add("@IDBus", SqlDbType.Int).Value = timetable.IDBus;
        cmd.Parameters.Add("@TimeStart", SqlDbType.DateTime).Value = timetable.TimeStart;
        cmd.Parameters.Add("@TimeStopExpected", SqlDbType.DateTime).Value = timetable.TimeStopExpected;
        cmd.Parameters.Add("@Direction", SqlDbType.NVarChar).Value = timetable.Direction;
        cmd.Parameters.Add("@Money", SqlDbType.Float).Value = timetable.Money;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }

    public DataSet getAllTimeTable()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllTimeTable", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "TimeTables");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet getAllBussActive()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllBussActive", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "BusActive");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet getAllBussNone()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllBussNone", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "DriverActive");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet getAllDriverActive()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllDriverActive", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "DriverActive");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet getAllDriverNone()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllDriverNone", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "DriverActive");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet getAllBusTripNone()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllBusTripNone", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "BusTripNone");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet getAllBusTripActive()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllBusTripActive", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "BusTripActive");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet searchTimeTable(String search)
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter sqlDb = new SqlDataAdapter("searchTimeTableByName", con);
            sqlDb.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@STRING_SEARCH", SqlDbType.NVarChar);
            param.Value = search;
            sqlDb.SelectCommand.Parameters.Add(param);

            sqlDb.Fill(ds, "BusRides");
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/

        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }
}